Mondial Database

Schema definitions

Table of contents

 

Index

Types

Tables

Type GeoCoord (description)

CREATE OR REPLACE TYPE GeoCoord AS OBJECT (
    Latitude NUMBER,
    Longitude NUMBER
);
/

Table Country (description)

CREATE TABLE Country (
    Name VARCHAR2(40)
        CONSTRAINT Country_Name_NotNull NOT NULL
        CONSTRAINT Country_Name_Unique UNIQUE,
    Code CHAR(2)
        CONSTRAINT Country_Key PRIMARY KEY,
    Capital VARCHAR2(40),
    Province VARCHAR2(40),
    Population NUMBER
        CONSTRAINT Country_Population_Check CHECK (
             Population >= 0
        ),
    Area NUMBER
        CONSTRAINT Country_Area_Check CHECK (
             Area >= 0
        )
);

Table Province (description)

CREATE TABLE Province (
    Name VARCHAR2(40),
    Country CHAR(2),
    Population NUMBER
        CONSTRAINT Province_Population_Check CHECK (
             Population >= 0
        ),
    Area NUMBER
        CONSTRAINT Province_Area_Check CHECK (
             Area >= 0
        ),
    Capital VARCHAR2(40),
    CapProv VARCHAR2(40),
    CONSTRAINT Province_Key PRIMARY KEY (Country, Name)
);

Table City (description)

CREATE TABLE City (
    Name VARCHAR2(40),
    Country CHAR(2),
    Province VARCHAR2(40),
    Population NUMBER
        CONSTRAINT City_Population_Check CHECK (
             Population >= 0
        ),
    Latitude NUMBER
        CONSTRAINT City_Latitude_Check CHECK (
             (Latitude >= -90) AND (Latitude <= 90)
        ),
    Longitude NUMBER
        CONSTRAINT City_Longitude_Check CHECK (
             (Longitude >= -180) AND (Longitude <= 180)
        ),
    CONSTRAINT City_Key PRIMARY KEY (Country, Province, Name)
);

Table Continent (description)

CREATE TABLE Continent (
    Name VARCHAR2(20)
        CONSTRAINT Continent_Key PRIMARY KEY,
    Area NUMBER
        CONSTRAINT Continent_Area_Check CHECK (
             Area >= 0
        )
);

Table encompasses (description)

CREATE TABLE encompasses (
    Country CHAR(2),
    Continent VARCHAR2(20),
    Percentage NUMBER
        CONSTRAINT encompasses_Percentage_Check CHECK (
             (Percentage > 0) AND (Percentage <= 100)
        ),
    CONSTRAINT encompasses_Key PRIMARY KEY (Continent, Country)
);

Table borders (description)

CREATE TABLE borders (
    Country1 CHAR(2),
    Country2 CHAR(2),
    Length NUMBER
        CONSTRAINT borders_Length_Check CHECK (
             Length > 0
        ),
    CONSTRAINT borders_Key PRIMARY KEY (Country1, Country2)
);

Table Organization (description)

CREATE TABLE Organization (
    Abbreviation VARCHAR2(15)
        CONSTRAINT Organization_Key PRIMARY KEY,
    Name VARCHAR2(100)
        CONSTRAINT Organization_Name_NotNull NOT NULL
        CONSTRAINT Organization_Name_Unique UNIQUE,
    Established DATE,
    City VARCHAR2(40),
    Province VARCHAR2(40),
    Country CHAR(2)
);

Table is_member (description)

CREATE TABLE is_member (
    Organization VARCHAR2(15),
    Country CHAR(2),
    Type VARCHAR2(30),
    CONSTRAINT is_member_Key PRIMARY KEY (Country, Organization)
);

Table Economy (description)

CREATE TABLE Economy (
    Country CHAR(2)
        CONSTRAINT Economy_Key PRIMARY KEY,
    GDP NUMBER
        CONSTRAINT Economy_GDP_Check CHECK (
             GDP >= 0
        ),
    Agriculture NUMBER,
    Industry NUMBER,
    Services NUMBER,
    Inflation NUMBER
);

Table Population (description)

CREATE TABLE Population (
    Country CHAR(2)
        CONSTRAINT Population_Key PRIMARY KEY,
    Population_Growth NUMBER,
    Infant_Mortality NUMBER
);

Table Politics (description)

CREATE TABLE Politics (
    Country CHAR(2)
        CONSTRAINT Politics_Key PRIMARY KEY,
    Independence DATE,
    Government VARCHAR2(120)
);

Table Language (description)

CREATE TABLE Language (
    Country CHAR(2),
    Name VARCHAR2(50),
    Percentage NUMBER
        CONSTRAINT Language_Percentage_Check CHECK (
             (Percentage > 0) AND (Percentage <= 100)
        ),
    CONSTRAINT Language_Key PRIMARY KEY (Country, Name)
);

Table Religion (description)

CREATE TABLE Religion (
    Country CHAR(2),
    Name VARCHAR2(50),
    Percentage NUMBER
        CONSTRAINT Religion_Percentage_Check CHECK (
             (Percentage > 0) AND (Percentage <= 100)
        ),
    CONSTRAINT Religion_Key PRIMARY KEY (Country, Name)
);

Table Ethnic_Group (description)

CREATE TABLE Ethnic_Group (
    Country CHAR(2),
    Name VARCHAR2(50),
    Percentage NUMBER
        CONSTRAINT Ethnic_Group_Percentage_Check CHECK (
             (Percentage > 0) AND (Percentage <= 100)
        ),
    CONSTRAINT Ethnic_Group_Key PRIMARY KEY (Country, Name)
);

Table located (description)

CREATE TABLE located (
    City VARCHAR2(40)
        CONSTRAINT located_City_NotNull NOT NULL,
    Province VARCHAR2(40)
        CONSTRAINT located_Province_NotNull NOT NULL,
    Country CHAR(2)
        CONSTRAINT located_Country_NotNull NOT NULL,
    River VARCHAR2(30),
    Lake VARCHAR2(30),
    Sea VARCHAR2(30)
);

Table River (description)

CREATE TABLE River (
    Name VARCHAR2(30)
        CONSTRAINT River_Key PRIMARY KEY,
    River VARCHAR2(30),
    Lake VARCHAR2(30),
    Sea VARCHAR2(30),
    Length NUMBER
        CONSTRAINT River_Length_Check CHECK (
             Length >= 0
        )
);

Table Mountain (description)

CREATE TABLE Mountain (
    Name VARCHAR2(30)
        CONSTRAINT Mountain_Key PRIMARY KEY,
    Height NUMBER
        CONSTRAINT Mountain_Height_Check CHECK (
             Height >= 0
        ),
    Coordinates GeoCoord
        CONSTRAINT Mountain_Coordinates_Check CHECK (
             (Coordinates.Longitude >= -180) AND
             (Coordinates.Longitude <= 180) AND
             (Coordinates.Latitude >= -90) AND
             (Coordinates.Latitude <= 90)
        )
);

Table Lake (description)

CREATE TABLE Lake (
    Name VARCHAR2(30)
        CONSTRAINT Lake_Key PRIMARY KEY,
    Area NUMBER
        CONSTRAINT Lake_Area_Check CHECK (
             Area >= 0
        )
);

Table Sea (description)

CREATE TABLE Sea (
    Name VARCHAR2(30)
        CONSTRAINT Sea_Key PRIMARY KEY,
    Depth NUMBER
        CONSTRAINT Sea_Depth_Check CHECK (
             Depth >= 0
        )
);

Table Island (description)

CREATE TABLE Island (
    Name VARCHAR2(30)
        CONSTRAINT Island_Key PRIMARY KEY,
    Islands VARCHAR2(30),
    Area NUMBER
        CONSTRAINT Island_Area_Check CHECK (
             Area >= 0
        ),
    Coordinates GeoCoord
        CONSTRAINT Island_Coordinates_Check CHECK (
             (Coordinates.Longitude >= -180) AND
             (Coordinates.Longitude <= 180) AND
             (Coordinates.Latitude >= -90) AND
             (Coordinates.Latitude <= 90)
        )
);

Table Desert (description)

CREATE TABLE Desert (
    Name VARCHAR2(30)
        CONSTRAINT Desert_Key PRIMARY KEY,
    Area NUMBER
        CONSTRAINT Desert_Area_Check CHECK (
             Area >= 0
        )
);

Table geo_river (description)

CREATE TABLE geo_river (
    River VARCHAR2(30),
    Country CHAR(2),
    Province VARCHAR2(40),
    CONSTRAINT geo_river_Key PRIMARY KEY (Country, Province, River)
);

Table geo_mountain (description)

CREATE TABLE geo_mountain (
    Mountain VARCHAR2(30),
    Country CHAR(2),
    Province VARCHAR2(40),
    CONSTRAINT geo_mountain_Key PRIMARY KEY (Country, Province, Mountain)
);

Table geo_lake (description)

CREATE TABLE geo_lake (
    Lake VARCHAR2(30),
    Country CHAR(2),
    Province VARCHAR2(40),
    CONSTRAINT geo_lake_Key PRIMARY KEY (Country, Province, Lake)
);

Table geo_sea (description)

CREATE TABLE geo_sea (
    Sea VARCHAR2(30),
    Country CHAR(2),
    Province VARCHAR2(40),
    CONSTRAINT geo_sea_Key PRIMARY KEY (Country, Province, Sea)
);

Table geo_island (description)

CREATE TABLE geo_island (
    Island VARCHAR2(30),
    Country CHAR(2),
    Province VARCHAR2(40),
    CONSTRAINT geo_island_Key PRIMARY KEY (Country, Province, Island)
);

Table geo_desert (description)

CREATE TABLE geo_desert (
    Desert VARCHAR2(30),
    Country CHAR(2),
    Province VARCHAR2(40),
    CONSTRAINT geo_desert_Key PRIMARY KEY (Country, Province, Desert)
);

Table merges_with (description)

CREATE TABLE merges_with (
    Sea1 VARCHAR2(30),
    Sea2 VARCHAR2(30),
    CONSTRAINT merges_with_Key PRIMARY KEY (Sea1, Sea2)
);